package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import db.oracle.OracleConnMgr;
import dto.PBoardDTO;
import dto.PBoard_regDTO;

public class PBoardDAO {
	public Vector getPboardList() throws SQLException {
		int res = 0;
		Vector pboardList = new Vector();
		OracleConnMgr mgr = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		OracleConnMgr.setDefaults();
		String selQuery = "SELECT t_project.f_project_seq, t_project.f_project_title,t_project.f_project_memb,t_project.f_project_read,t_project.f_project_endyear,t_project.f_project_endmonth,t_project.f_project_endday,t_project.f_member_id FROM t_project,t_member WHERE ((t_member.f_member_id = t_project.f_member_id))";
		try {
			mgr = OracleConnMgr.getInstance();
			conn = mgr.getConnection();
			pstmt = conn.prepareStatement(selQuery);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				PBoardDTO pDTO = new PBoardDTO();
				pDTO.setPboardSeq(rs.getInt("f_project_seq"));
				pDTO.setPboardTitle(rs.getString("f_project_title"));
				pDTO.setPboardEndyear(rs.getString("f_project_endyear"));
				pDTO.setPboardEndMonth(rs.getString("f_project_endmonth"));
				pDTO.setPboardEndday(rs.getString("f_project_endday"));
				pDTO.setPboardRead(rs.getInt("f_project_read"));
				pDTO.setPboardMemId(rs.getString("f_member_id"));
				pboardList.addElement(pDTO);
			}
		} catch (SQLException e1) {
			System.out.println(e1.toString());
		} finally {
			mgr.close(conn, pstmt, rs);
		}
		return pboardList;
	}

	public PBoardDTO getPboard(int seq) throws SQLException {
		PBoardDTO pDTO = new PBoardDTO();
		OracleConnMgr mgr = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		OracleConnMgr.setDefaults();
		String readQuery = "SELECT t_project.f_project_seq, t_project.f_project_title,t_project.f_project_memb, t_project.f_project_read,t_project.f_project_content, t_project.f_project_endyear,t_project.f_project_endmonth, t_project.f_project_endday,t_project.f_member_id FROM t_project, t_member WHERE ((t_member.f_member_id = t_project.f_member_id)AND((t_project.f_project_seq = ?)))";
		try {
			mgr = OracleConnMgr.getInstance();
			conn = mgr.getConnection();
			pstmt = conn.prepareStatement(readQuery);
			System.out.println(seq);
			pstmt.setInt(1, seq);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				pDTO.setPboardContent((rs.getString("f_project_content")));
				pDTO.setPboardSeq(rs.getInt("f_project_seq"));
				pDTO.setPboardTitle(rs.getString("f_project_title"));
				pDTO.setPboardEndyear(rs.getString("f_project_endyear"));
				pDTO.setPboardEndMonth(rs.getString("f_project_endmonth"));
				pDTO.setPboardEndday(rs.getString("f_project_endday"));
				pDTO.setPboardRead(rs.getInt("f_project_read"));
				pDTO.setPboardMemId(rs.getString("f_member_id"));
				pDTO.setPboardRead(rs.getInt("f_project_read"));
				pDTO.setPboardMemb((rs.getInt("f_project_memb")));
			}
		} catch (SQLException e1) {
			System.out.println(e1.toString());
		} finally {
			mgr.close(conn, pstmt, rs);
		}
		return pDTO;
	}

	public int updateReadCnt(int readCnt, int seq) throws SQLException {
		OracleConnMgr mgr = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		OracleConnMgr.setDefaults();
		String updateQuery = "UPDATE t_project SET t_project.f_project_read=? WHERE t_project.f_project_seq=?";
		int res = 0;
		try {
			mgr = OracleConnMgr.getInstance();
			conn = mgr.getConnection();
			pstmt = conn.prepareStatement(updateQuery);
			System.out.println(readCnt);
			pstmt.setInt(1, readCnt + 1);
			pstmt.setInt(2, seq);
			res = pstmt.executeUpdate();
		} catch (SQLException e1) {
			System.out.println(e1.toString());
		} finally {
			mgr.close(conn, pstmt, rs);
		}
		if (res > 0)
			return 1;
		else
			return 0;
	}

	public int insertPboard(PBoardDTO pDTO) throws SQLException {
		OracleConnMgr mgr = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		OracleConnMgr.setDefaults();
		String insertQuery = "INSERT INTO t_project(f_project_seq,f_member_id,f_project_title,f_project_memb,f_project_read,f_project_content,f_project_endyear,f_project_endmonth,f_project_endday) VALUES(PBOARD_SEQ.nextval,?,?,?,?,?,?,?,?)";
		int res = 0;
		try {
			mgr = OracleConnMgr.getInstance();
			conn = mgr.getConnection();
			pstmt = conn.prepareStatement(insertQuery);
			pstmt.setString(5, pDTO.getPboardContent());
			pstmt.setString(1, pDTO.getPboardMemId());
			pstmt.setString(2, pDTO.getPboardTitle());
			pstmt.setInt(3, 0);
			pstmt.setInt(4, 0);
			pstmt.setInt(6, Integer.parseInt(pDTO.getPboardEndyear()));
			pstmt.setInt(7, Integer.parseInt(pDTO.getPboardEndMonth()));
			pstmt.setInt(8, Integer.parseInt(pDTO.getPboardEndday()));
			System.out.println(pDTO.getPboardContent());
			System.out.println(pDTO.getPboardMemId());
			System.out.println(pDTO.getPboardTitle());
			System.out.println(Integer.parseInt(pDTO.getPboardEndyear()));
			System.out.println(Integer.parseInt(pDTO.getPboardEndMonth()));
			System.out.println(Integer.parseInt(pDTO.getPboardEndday()));
			res = pstmt.executeUpdate();
		} catch (SQLException e1) {

		} catch (Exception e) {

		} finally {
			mgr.close(conn, pstmt);
		}
		return res;
	}

	public int applyProject(PBoard_regDTO prDTO) throws SQLException {
		OracleConnMgr mgr = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		OracleConnMgr.setDefaults();
		String insertQuery = "INSERT INTO t_project_register(f_project_register_seq,f_project_register_hope,f_project_register_lang,f_project_seq,f_member_id) VALUES(PBOARD_REG_SEQ.nextval,?,?,?,?)";
		int res = 0;
		try {
			mgr = OracleConnMgr.getInstance();
			conn = mgr.getConnection();
			pstmt = conn.prepareStatement(insertQuery);
			pstmt.setString(1, prDTO.getPboardRegHope());
			pstmt.setString(2, prDTO.getPboardRegLang());
			pstmt.setInt(3, prDTO.getPboardSeq());
			pstmt.setString(4, prDTO.getPboardRegMemId());
			res = pstmt.executeUpdate();
		} catch (SQLException e1) {
			System.out.println(e1.toString());
		} catch (Exception e) {
			System.out.println(e.toString());
		} finally {
			mgr.close(conn, pstmt);
		}
		return res;
	}

	public Vector getRegMemList(int pboardSeq) throws SQLException {
		int res = 0;
		Vector vector = new Vector();
		OracleConnMgr mgr = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		OracleConnMgr.setDefaults();
		String selQuery = "SELECT t_project_register.f_project_register_seq,t_project_register.f_project_register_lang,t_project_register.f_project_register_hope,t_project_register.f_member_id, t_project_register.f_project_seq FROM t_project_register, t_project, t_member WHERE ((t_project.f_project_seq = t_project_register.f_project_seq) AND (t_project.f_member_id = t_project_register.f_member_id) AND (t_member.f_member_id = t_project_register.f_member_id) AND (t_member.f_member_id = t_project.f_member_id) AND ((t_project_register.f_project_seq = ?)))";
		try {
			mgr = OracleConnMgr.getInstance();
			conn = mgr.getConnection();
			pstmt = conn.prepareStatement(selQuery);
			pstmt.setInt(1, pboardSeq);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				PBoard_regDTO prDTO = new PBoard_regDTO();
				prDTO
						.setPboardRegHope((rs
								.getString("f_project_register_hope")));
				prDTO
						.setPboardRegLang((rs
								.getString("f_project_register_lang")));
				prDTO.setPboardRegMemId((rs.getString("f_member_id")));
				prDTO.setPboardRegSeq((rs.getInt("f_project_register_seq")));
				prDTO.setPboardSeq((rs.getInt("f_project_seq")));
				vector.addElement(prDTO);
			}
		} catch (SQLException e1) {
			System.out.println(e1.toString());
		} finally {
			mgr.close(conn, pstmt, rs);
		}
		return vector;
	}

	public int deletePboard(int pboardSeq) throws SQLException {
		int res = 0;
		OracleConnMgr mgr = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		String delRegQuery = "DELETE FROM t_project_register WHERE f_project_seq=?";
		String delQuery = "DELETE FROM t_project WHERE f_project_seq=?";
		try {
			mgr = OracleConnMgr.getInstance();
			conn = mgr.getConnection();
			pstmt = conn.prepareStatement(delRegQuery);
			pstmt.setInt(1, pboardSeq);
			res = pstmt.executeUpdate();
			// delete pboard
			pstmt = conn.prepareStatement(delQuery);
			pstmt.setInt(1, pboardSeq);
			res = pstmt.executeUpdate();
			// delete pboardSeq
		} catch (SQLException e1) {
			System.out.println(e1.toString());
		} catch (Exception e) {
			System.out.println(e.toString());
		} finally {
			mgr.close(conn, pstmt);
		}
		return res;
	}

	/**
	 * update memb count
	 * 
	 * @param pboardSeq
	 * @return
	 * @throws SQLException
	 */
	public int updateMembCnt(int pboardSeq) throws SQLException {
		OracleConnMgr mgr = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		String selQuery = "SELECT f_project_memb FROM t_project where f_project_seq=?";
		String updateQuery = "UPDATE t_project SET f_project_memb=? WHERE f_project_seq=?";
		int res = 0, memb = 0;
		ResultSet rs = null;
		try {
			mgr = OracleConnMgr.getInstance();
			conn = mgr.getConnection();
			pstmt = conn.prepareStatement(selQuery);
			pstmt.setInt(1, pboardSeq);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				memb = rs.getInt("f_project_memb");
			}
			// get project memb and set memb+1
			pstmt = conn.prepareStatement(updateQuery);
			pstmt.setInt(1, ++memb);
			pstmt.setInt(2, pboardSeq);
			res = pstmt.executeUpdate();
		} catch (SQLException e1) {
			System.out.println(e1.toString());
		} finally {
			mgr.close(conn, pstmt, rs);
		}
		return res;
	}

	public int updatePboard(PBoardDTO pDTO) throws SQLException {
		OracleConnMgr mgr = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		String updateQuery = "UPDATE t_project SET f_project_title=?, f_project_content=? WHERE f_project_seq=?";
		int res = 0;
		try {
			mgr = OracleConnMgr.getInstance();
			conn = mgr.getConnection();
			pstmt = conn.prepareStatement(updateQuery);
			pstmt.setString(2, pDTO.getPboardContent());
			pstmt.setString(1, pDTO.getPboardTitle());
			pstmt.setInt(3, pDTO.getPboardSeq());
			res = pstmt.executeUpdate();
		} catch (SQLException e1) {
			System.out.println(e1.toString());
		} catch (Exception e) {
			System.out.println(e.toString());
		} finally {
			mgr.close(conn, pstmt);
		}
		return 0;
	}
}
